#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import sys
import multiprocessing
import _mysql, _mysql_exceptions
from collections import defaultdict
from pytools import mysqltool
from pytools import hbaseservice

def connect_to_localtest(db='test'):
    host = '10.5.20.16'
    port = 58885
    user = 'ec_stat'
    passwd = 'L#c8UM5RGh5C'
    dbh = _mysql.connect(host, user, passwd, db, port)
    return dbh
#get table_prefix
def get_channel_info():
    q = 'select channel_id, table_prefix from channel_define'
    info = {}
    conn = mysqltool.connect_to_base_dicts()
    r = mysqltool.query(conn, q)
    for one in r:
        info[one[0]] = one[1]
    conn.close()
    return info

fields = ['stat_date', 'dispcity1', 'dispcity2', 'dispcity3', 'belong_cate1', 'belong_cate2', 'total_info_count', 'total_info_users', 'valid_info_count', 'valid_info_users', 'new_info_count', 'new_info_users', 'modify_info_count', 'modify_info_users', 'refresh_info_count', 'refresh_info_users', 'new_valid_info_count', 'new_valid_info_users', 'ordinary_valid_info_count', 'ordinary_valid_info_users', 'ordinary_new_info_count', 'ordinary_new_info_users', 'ordinary_modify_info_count', 'ordinary_modify_info_users', 'ordinary_refresh_info_count', 'ordinary_refresh_info_users', 'ordinary_new_valid_info_count', 'ordinary_new_valid_info_users', 'vip_valid_info_count', 'vip_valid_info_users', 'vip_new_info_count', 'vip_new_info_users', 'vip_modify_info_count', 'vip_modify_info_users', 'vip_refresh_info_count', 'vip_refresh_info_users', 'vip_new_valid_info_count', 'vip_new_valid_info_users']

def get_detail_fieldname_index():
       return dict(zip(fields, range(len(fields))))

def get_info(q):
    info = {}
    conn = mysqltool.connect_to_58_stats()
    r = mysqltool.query(conn, q)
    for one in r:
        info[one[0]] = one[1:]
    # print len(info)
    conn.close()
    return info

def get_cate_info():
     sql = 'select cate_id, cate1, cate2, channel_id from belong_cate where cate3_id=0'
     return get_info(sql)

def get_displocal_info():
    q = 'select local_id, city1, city2, city3 from display_local where city4_id=0'
    return get_info(q)
            
onetwothree = ['first', 'second', 'third']
fieldname2index = get_detail_fieldname_index()
num2level = dict(zip(range(1, 4), onetwothree))
counttype = ['InfoCount', 'UserCount']

states = ['state1', 'uptype0', 'uptype1', 'uptype2', 'state1_uptype0']
types = ['valid', 'new', 'modify', 'refresh', 'new_valid']
states2types = dict(zip(states, types))

sources = ['source0', 'source6']
roles = ['ordinary', 'vip'] 
sources2roles = dict(zip(sources, roles))

def writeDetailToLocal(file,rowdata):
    txt = ','.join(rowdata) + '\n'
    file.write(txt)
    
def load_detail_to_mysql(filename,tableprefix):
        tablename = tableprefix + '_post_detail'
        loaddata = 'load data low_priority local infile "%s" into table %s fields terminated by ",";' % (filename, tablename)
        cmdtxt = 'echo \'%s\' | mysql --auto-rehash -h10.5.20.16 -P58885 -u"ec_stat" -p"L#c8UM5RGh5C" -D test' % (loaddata)
        os.system(cmdtxt)
        os.system('rm ' + filename)


def writeDetailToMysql(connect,taleprefix,rowdata):    
    tablename = taleprefix + '_post_detail'
    mysqltool.insertinto(connect, tablename, fields, rowdata)

def writeSummaryToMysql(day):
    connect=connect_to_localtest()
    hbase_service = hbaseservice.HBaseService()
    rowkey='A_'+day
    rs = hbase_service.queryByRowKey('FourthInfo', rowkey)
    if not rs:
        return
    tablename = 'post_summary'
    fields = ['stat_date', 'infos', 'users']
    values = [day,rs.get('cf:InfoCount'),rs.get('cf:UserCount')]
    mysqltool.insertinto(connect, tablename, fields, values)
    connect.close()
    hbase_service.shutdown


def set_rowdata(rowdata,hbase_service,fieldname,key):
    rowkey = '_'.join((key))
#    print rowkey
    rs = hbase_service.queryByRowKey('FourthInfo', rowkey)
    if rs and rs.get('cf:InfoCount'):
        rowdata[fieldname2index[fieldname+'_info_count']] = rs.get('cf:InfoCount')
        rowdata[fieldname2index[fieldname+'_info_users']] = rs.get('cf:UserCount')
        return True

def compose_mysql_rows(day, cateinfos, localinfos,channel_info):
    mysqlConnect = connect_to_localtest()
    hbase_service = hbaseservice.HBaseService()
#    print hbase_service.queryByRowKey('FourthInfo','catefirst1_localfirst1_state1_uptype0_20121126')
#    rowdata = ['20121220', '北京', '朝阳', '北苑', '房产', '租房', '2', '2', '1', '1', '0', '0', '1', '0', '0', '1', '1', '1', '1', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0']
    for cateid, cinfo in cateinfos:
        cate_level = 'cate' + num2level[2 - cinfo.count('')]+cateid
        print cate_level
#        filename = 'data/%s-%s.txt' % (day, cateid)
#        file=open(filename,'wb')
        for localid, linfo in localinfos.items():
            rowdata = ['0'] * len(fields)
            rowdata[fieldname2index['stat_date']] = day
            local_level = 'local' + num2level[3 - linfo.count('')]+localid
            key=(cate_level,local_level,day)
            if not set_rowdata(rowdata,hbase_service,'total',key):
                continue
            b = fieldname2index['belong_cate1']
            for i, a in enumerate(cinfo[:2]):
                rowdata[b + i] = a
            b = fieldname2index['dispcity1']   
            for i, a in enumerate(linfo):
                rowdata[b + i] = a         
            for state in states:
                statekey=(cate_level, local_level, state, day)
                set_rowdata(rowdata,hbase_service,states2types[state],statekey)
                for source in sources:
                    sourcekey = (cate_level, local_level, state, source, day)
                    set_rowdata(rowdata,hbase_service,sources2roles[source] + '_' + states2types[state],sourcekey)
#            print(rowdata)
            writeDetailToMysql(mysqlConnect,channel_info[cinfo[2]],rowdata)
#            writeDetailToLocal(file,rowdata)
#        file.close() 
#        load_detail_to_mysql(filename,channel_info[cinfo[2]])
#    writeDetailToMysql(mysqlConnect,channel_info[cinfo[2]],rowdata)
    mysqlConnect.close()
    hbase_service.shutdown()

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print 'usage: %s day' % (sys.argv[0])
        sys.exit(1)
#        
    day = sys.argv[1]
    print(day)
    # day = '20121220'
    # infile = 'data/hoho.txt'
#    infile = 'data/' + day + '.txt'  
    channel_info=get_channel_info()
    cate_infos = get_cate_info().items()
    local_infos = get_displocal_info()
    
    cate_infos=cate_infos[:20]
    cate_info_len=len(cate_infos)
    avg_len = cate_info_len//10
    
    pool = multiprocessing.Pool(processes=4)
#    channel_info=multiprocessing.Manager().dict(channel_info)
#    print channel_info.items()
    i=0
    while(i<9):
        pool.apply_async(compose_mysql_rows, (day,cate_infos[i*avg_len:(i+1)*avg_len],local_infos,channel_info))
        i+=1
    pool.apply_async(compose_mysql_rows, (day,cate_infos[i*avg_len:],local_infos,channel_info))
    pool.close()
    pool.join()
#    compose_mysql_rows(day,cate_infos[i*avg_len:],local_infos,channel_info)
    writeSummaryToMysql(day)
    sys.exit(0)